/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.seagate.app.manager;

import com.seagate.app.bean.MailGroupBean;
import com.seagate.util.db.OracleConnectionPool;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author 452530
 */
public class MailListManager {
  public List getMailList() {
        List maillist=new ArrayList();
        String sql = "SELECT MAIL FROM FDB_EPS_MAIL ";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                maillist.add(rset.getString("MAIL"));  
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return maillist;
    }
  public List getAutoMailList() {
        List maillist=new ArrayList();
        String sql = "SELECT MAIL FROM FDB_EPS_AUTOMAIL ";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                maillist.add(rset.getString("MAIL"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return maillist;
    }
  public List getMailListByGroupID(int id) {
        List maillist=new ArrayList();
        String sql = "SELECT MAIL FROM FDB_EPS_MAIL WHERE MAIL_GROUP_ID='"+id+"' ";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                maillist.add(rset.getString("MAIL"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return maillist;
    }
  public List getMailGroupList() {
        List maillist=new ArrayList();
        String sql = "SELECT * FROM FDB_EPS_MAIL_GROUP ";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                MailGroupBean mg=new MailGroupBean();
                mg.setMail_group_id(rset.getInt("MAIL_GROUP_ID"));
                mg.setMail_group_name(rset.getString("MAIL_GROUP_NAME"));
                mg.setMail_group_comment(rset.getString("MAIL_GROUP_COMMENT"));
                
                maillist.add(mg);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return maillist;
    }
  public MailGroupBean getMailGroupID(String id) {
        MailGroupBean mg=new MailGroupBean();
        String sql = "SELECT * FROM FDB_EPS_MAIL_GROUP WHERE MAIL_GROUP_ID='"+id+"' ";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                
                mg.setMail_group_id(rset.getInt("MAIL_GROUP_ID"));
                mg.setMail_group_name(rset.getString("MAIL_GROUP_NAME"));
                mg.setMail_group_comment(rset.getString("MAIL_GROUP_COMMENT"));


            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return mg;
    }
   public boolean deleteMail(String mail) {
        boolean result = false;
        String sql = "";
          sql = "delete FDB_EPS_MAIL where MAIL = '" + mail + "' ";


        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
   public boolean deleteAutoMail(String mail) {
        boolean result = false;
        String sql = "";
          sql = "delete FDB_EPS_AUTOMAIL where MAIL = '" + mail + "' ";


        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
   public boolean addMail(String mail,String mailgroup) {
        boolean result = false;
        String sql = "";
        sql = "insert into FDB_EPS_MAIL (MAIL,MAIL_GROUP_ID) "
                    + " values ('" + mail + "','"+mailgroup+"')";

        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
   public boolean addAutoMail(String mail) {
        boolean result = false;
        String sql = "";
        sql = "insert into FDB_EPS_AUTOMAIL (MAIL) "
                    + " values ('" + mail + "')";

        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
   public boolean checkMail(String mail) {

        String sql = "SELECT * FROM FDB_EPS_MAIL WHERE MAIL='"+mail+"'";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {

                return true;




            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return false;
    }
   public boolean checkAutoMail(String mail) {

        String sql = "SELECT * FROM FDB_EPS_AUTOMAIL WHERE MAIL='"+mail+"'";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {

                return true;




            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return false;
    }
}
